﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DBEngine.Elements;

namespace DBEngine.Tables
{
	abstract class Table
	{
		readonly static string ID_COL_NAME;
		readonly static SqlDbType ID_COL_TYPE;
		protected readonly string mTableName;

		static Table()
		{
			ID_COL_NAME = "ID";
			ID_COL_TYPE = SqlDbType.Int;
		}
		protected Table(string mTableName)
		{
			this.mTableName = mTableName;
		}

		protected abstract string SetInsertQuery(Element newElement, string cols, string values, List<SqlParameter> paramsList);
		protected abstract string SetUpdateQuery(Element element, List<SqlParameter> paramsList);



		internal Boolean Insert(Element newElement)
		{
			Boolean retval = false;

			if (newElement == null)
				return retval;


			string queryString = @"Insert into " + mTableName + " ";
			string cols = "(", values = "Values(";
			List<SqlParameter> paramsList = new List<SqlParameter>();


			string insertQuery = SetInsertQuery(newElement, cols, values, paramsList);

			queryString += insertQuery;

			SqlCommand command = DBConnection.getCommand();
			command.CommandText = queryString;
			foreach (SqlParameter parameter in paramsList)
			{
				command.Parameters.Add(parameter);
			}

			Object dbResult = command.ExecuteScalar();
			if (dbResult == null)
				return retval;

			retval = true;
			newElement.SetID(Convert.ToInt16(dbResult));


			return retval;

		}

		internal Boolean Update(Element element)
		{
			Boolean retval = false;

			if (element == null)
				return retval;

			string queryString = @"Update  " + mTableName + " ";

			List<SqlParameter> paramsList = new List<SqlParameter>();
			string updateString = SetUpdateQuery(element, paramsList);

			queryString += updateString + "  where ID =@IDParam";
			SqlParameter param = new SqlParameter("@IDParam", ID_COL_TYPE);
			param.Value = element.getID();
			paramsList.Add(param);

			SqlCommand command = DBConnection.getCommand();
			command.CommandText = queryString;
			foreach (SqlParameter parameter in paramsList)
			{
				command.Parameters.Add(parameter);
			}

			command.ExecuteNonQuery();
			return true;
		}

		protected abstract Object ReadRow(SqlDataReader reader);

		internal Object Retrive(int id)
		{
			Object retval = new Object();
			string queryString = @"Select * From " + mTableName + " Where " + ID_COL_NAME + "=@IDParam ";

			SqlParameter param = new SqlParameter("@IDParam", ID_COL_TYPE);
			param.Value = id;

			SqlCommand command = DBConnection.getCommand();
			command.CommandText = queryString;
			command.Parameters.Add(param);

			SqlDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				retval = ReadRow(reader);
			}
			reader.Close();
			return retval;
		}

		static void Main()
		{

			GlassesOrderTable table = new GlassesOrderTable();
			EyeGlasses glasses1 = new EyeGlasses();
			EyeGlasses glasses2 = new EyeGlasses();
			EyeGlasses glasses3 = new EyeGlasses();
			EyeGlasses glasses4 = new EyeGlasses();
			int i = 0;
			foreach (EyeGlasses.Fields glassesField in Enum.GetValues(typeof(EyeGlasses.Fields)))
			{
				if (glassesField == EyeGlasses.Fields.Frame )
				{
					glasses1[EyeGlasses.Fields.Frame] = "frame 1";
					glasses2[EyeGlasses.Fields.Frame] = "frame 2";
					glasses3[EyeGlasses.Fields.Frame] = "frame 3";
					glasses4[EyeGlasses.Fields.Frame] = "frame 4";
                    continue;

				}

				if (i % 2 == 0)
					glasses2[glassesField] = false;
				else
					glasses3[glassesField] = true;
				glasses4[glassesField] = true;

				i++;
			}

			Order order = new Order(1, 2, DateTime.Now, 1, glasses1);
			Console.WriteLine(table.Insert(order));

			order[Order.Fields.OrderElement] = glasses2;
			Console.WriteLine(table.Insert(order));

			order[Order.Fields.OrderElement] = glasses3;
			Console.WriteLine(table.Insert(order));

			order[Order.Fields.OrderElement] = glasses4;
			Console.WriteLine(table.Insert(order));

			order[Order.Fields.ID] = 1;
			order[Order.Fields.OrderElement] = glasses4;
			Console.WriteLine(table.Update(order));

			Order ret = (Order)table.Retrive(2);




			LensesOrderTable tablee = new LensesOrderTable();

			Lenses lensOrder = new Lenses(Lenses.Types.Disposable, Lenses.Brands.Dispo);
			Order order2, ordere = new Order(1, 1, DateTime.Now, 22, lensOrder);
			Console.WriteLine(tablee.Insert(ordere));

			lensOrder[Lenses.Fields.Type] = Lenses.Types.Toric;
			tablee.Update(ordere);

			lensOrder[Lenses.Fields.Type] = Lenses.Types.Permenant;

			order2 = (Order)tablee.Retrive(1);

			ClientInfo client = new ClientInfo("Mohammad", "Muhissen", "Dovnov", "02-5829690", "0545412885", "subway.1984@gmail.com", new DateTime(1984, 7, 4), ClientInfo.GenderEnum.Male, 8967976);
			ClientInfo client2 = new ClientInfo("Mohammad2", "Muhissen", null, null, "0545412885", null, new DateTime(1984, 7, 4), ClientInfo.GenderEnum.Male, 896117976);

			ClientInfoTable clientTable = new ClientInfoTable();
			Console.WriteLine(clientTable.Insert(client));
			Console.WriteLine(clientTable.Insert(client2));

			client[ClientInfo.Fields.FirstName] = "Tony";
			clientTable.Update(client);

			client2 = (ClientInfo)(clientTable.Retrive(1));





			Dictionary<Enum, ExaminationElement> Values = new Dictionary<Enum, ExaminationElement>();
			foreach (Examination.Fields filed in Enum.GetValues(typeof(Examination.Fields)))
			{
				if (filed == Examination.Fields.ID || filed == Examination.Fields.ClientID || filed == Examination.Fields.Date)
					continue;
				ExaminationElement examelem = new ExaminationElement();
				examelem[ExaminationElement.Fields.OD] = 2.12;
				examelem[ExaminationElement.Fields.OS] = 3.3;
				Values.Add(filed, examelem);
			}

			GlasesExamination glass = new GlasesExamination(DateTime.Now, 1, Values);
			LensesExamination lensses = new LensesExamination(DateTime.Now, 2, new Dictionary<Enum, ExaminationElement>(Values));

			GlassesExaminationTable gtable = new GlassesExaminationTable();
			Console.WriteLine(gtable.Insert(glass));
			Console.WriteLine(glass);
			ExaminationElement e = (ExaminationElement)glass[Examination.Fields.CYLINDER];
			e[ExaminationElement.Fields.OD] = 7.77;
			Console.WriteLine(gtable.Update(glass));
			Console.WriteLine(glass);

			LensesExaminationTable ltable = new LensesExaminationTable();
			Console.WriteLine(ltable.Insert(lensses));
			Console.WriteLine(lensses);
			((ExaminationElement)(lensses[Examination.Fields.DIA]))[ExaminationElement.Fields.OD] = 5.55;
			Console.WriteLine(ltable.Update(lensses));
			Console.WriteLine(lensses);


		}
	}


}
